﻿using System.Collections.Generic;
using System.Linq;
using System.Runtime.Remoting.Contexts;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System;
using KlauserswagModel;


namespace WebShopDAL
{
    public static class OrderHeadDB
    {

        private static KlauserswagEntities _context = new KlauserswagEntities();
        /// <summary>
        /// Creates a new OrderHead.
        /// </summary>
        /// <param name="orderHead">The new orderhead.</param>
        /// <returns>Int value of affected rows.</returns>
        public static int CreateOrderHead(OrderHead orderHead)
        {
            try
            {
                var context = new KlauserswagEntities();
                context.OrderHeads.Add(orderHead);
                return context.SaveChanges();

                //var sqlQuery = new StringBuilder()
                //.Append("INSERT INTO [dbo].[OrderHead]([UserID],[Address] ,[Zip],[City],[Processed]) ")
                //.Append("VALUES(@UserID,@Address,@Zip,@City,@Processed)");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderHeadDB, CreateOrderHead");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("UserID", SqlDbType.Int).Value = orderHead.UserID;
                //        command.Parameters.Add("Address", SqlDbType.NChar).Value = orderHead.Address;
                //        command.Parameters.Add("Zip", SqlDbType.NChar).Value = orderHead.Zip;
                //        command.Parameters.Add("City", SqlDbType.NChar).Value = orderHead.City;
                //        command.Parameters.Add("Processed", SqlDbType.NChar).Value = orderHead.Processed;

                //        return command.ExecuteNonQuery();
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderHeadDB, CreateOrderHead", ex);
            }
        }

        /// <summary>
        /// List all OrderHeads
        /// </summary>
        /// <returns>List of all OrderHeads</returns>
        public static List<OrderHead> GetAllOrderHead()
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.OrderHeads.ToList();
                return query;

                //var sqlQuery = new StringBuilder()
                //.Append("SELECT [OrderID], [UserID],[Address] ,[Zip],[City],[Processed]")
                //.Append("FROM [dbo].[OrderHead]");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        if (connection == null)
                //            throw new Exception("Connection is null in OrderHeadDB, GetAllOrderHead");

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        List<OrderHead> orderHeads = new List<OrderHead>();
                //        if (dataSet.Tables[0].Rows.Count > 0)
                //        {
                //            for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                //            {
                //                orderHeads.Add(new OrderHead(
                //                     orderID: int.Parse(dataSet.Tables[0].Rows[i]["OrderID"].ToString().Trim()),
                //            userID: int.Parse(dataSet.Tables[0].Rows[i]["UserID"].ToString().Trim()),
                //            address: dataSet.Tables[0].Rows[i]["Address"].ToString().Trim(),
                //            zip: dataSet.Tables[0].Rows[i]["Zip"].ToString().Trim(),
                //            city: dataSet.Tables[0].Rows[i]["City"].ToString().Trim(),
                //            processed: dataSet.Tables[0].Rows[i]["Processed"].ToString().Trim()
                //            ));
                //            }

                //            return orderHeads;
                //        }
                //        return orderHeads;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderHeadDB, GetAllOrderHead", ex);
            }
        }

        /// <summary>
        /// Gets an OrderHead by its OrderID.
        /// </summary>
        /// <param name="orderID">The orderID for the OrderHead</param>
        /// <returns>The OrderHead.</returns>
        public static OrderHead GetOrderHeadByOrderID(int orderID)
        {
            try
            {
                //var context = new KlauserswagEntities();
                return _context.OrderHeads.Where(o => o.OrderID == orderID).ToList().First();

                //var sqlQuery = new StringBuilder()
                //.Append("SELECT [OrderID], [UserID],[Address] ,[Zip],[City],[Processed] ")
                //.Append("FROM [dbo].[OrderHead] ")
                //.Append("WHERE [OrderID] = @OrderID");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderHeadDB, GetOrderHeadByOrderID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("OrderID", SqlDbType.Int).Value = orderID;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);


                //        if (dataSet.Tables[0].Rows.Count > 0)
                //        {
                //            return new OrderHead(
                //                                        orderID: int.Parse(dataSet.Tables[0].Rows[0]["OrderID"].ToString().Trim()),
                //                               userID: int.Parse(dataSet.Tables[0].Rows[0]["UserID"].ToString().Trim()),
                //                               address: dataSet.Tables[0].Rows[0]["Address"].ToString().Trim(),
                //                               zip: dataSet.Tables[0].Rows[0]["Zip"].ToString().Trim(),
                //                               city: dataSet.Tables[0].Rows[0]["City"].ToString().Trim(),
                //                               processed: dataSet.Tables[0].Rows[0]["Processed"].ToString().Trim()
                //                               );
                //        }
                //        return new OrderHead();
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderHeadDB, GetOrderHeadByOrderID", ex);
            }
        }

        /// <summary>
        /// Gets the OrderID for the latest added OrderHead.
        /// </summary>
        /// <param name="userID">The userID for the user.</param>
        /// <returns>The lastest added OrderHead for the userID.</returns>
        public static OrderHead GetOrderHeadByUserID(int userID)
        {
            try
            {
                return _context.OrderHeads.Where(o => o.UserID == userID).ToList().First();

                //var sqlQuery = new StringBuilder()
                //.Append("SELECT TOP 1 [OrderID], [UserID],[Address] ,[Zip],[City],[Processed] ")
                //.Append("FROM [dbo].[OrderHead] ")
                //.Append("WHERE [UserID] = @UserID ")
                //.Append("ORDER BY [OrderID] DESC");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderHeadDB, GetOrderHeadByUserID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("UserID", SqlDbType.Int).Value = userID;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);


                //        if (dataSet.Tables[0].Rows.Count > 0)
                //        {
                //            return new OrderHead(
                //                                        orderID: int.Parse(dataSet.Tables[0].Rows[0]["OrderID"].ToString().Trim()),
                //                               userID: int.Parse(dataSet.Tables[0].Rows[0]["UserID"].ToString().Trim()),
                //                               address: dataSet.Tables[0].Rows[0]["Address"].ToString().Trim(),
                //                               zip: dataSet.Tables[0].Rows[0]["Zip"].ToString().Trim(),
                //                               city: dataSet.Tables[0].Rows[0]["City"].ToString().Trim(),
                //                               processed: dataSet.Tables[0].Rows[0]["Processed"].ToString().Trim()
                //                               );
                //        }
                //        return new OrderHead();
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderHeadDB, GetOrderHeadByUserID", ex);
            }
        }

        /// <summary>
        /// Updates a orderHead on its OrderID
        /// </summary>
        /// <param name="orderHead">Orderhead to update</param>
        /// <returns>Int value of rows affected</returns>
        public static int UpdateOrderHead(OrderHead orderHead)
        {
            try
            {
                var query =_context.OrderHeads.Where(o => o.OrderID == orderHead.OrderID);
                return _context.SaveChanges();

                //var sqlQuery = new StringBuilder()
                //.Append("UPDATE [dbo].[OrderHead] ")
                //.Append("SET [Address] = @Address,[Zip] = @Zip,[City] = @City,[Processed] = @Processed ")
                //.Append("WHERE [OrderID] = @OrderID");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderHeadDB, UpdateOrderHead");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("OrderID", SqlDbType.Int).Value = orderHead.OrderID;
                //        command.Parameters.Add("Address", SqlDbType.NChar).Value = orderHead.Address;
                //        command.Parameters.Add("Zip", SqlDbType.NChar).Value = orderHead.Zip;
                //        command.Parameters.Add("City", SqlDbType.NChar).Value = orderHead.City;
                //        command.Parameters.Add("Processed", SqlDbType.NChar).Value = orderHead.Processed;

                //        return command.ExecuteNonQuery();
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderHeadDB, UpdateOrderHead", ex);
            }
        }

        /// <summary>
        /// Deletes an OrderHead
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns>Int value of rows affected</returns>
        public static int DeleteOrderHead(int orderID)
        {
            try
            {
                var query = _context.OrderHeads.Where(o => o.OrderID == orderID);
                return _context.SaveChanges();
                //var sqlQuery = new StringBuilder()
                //.Append("DELETE FROM [dbo].[OrderHead]")
                //.Append("WHERE [OrderID] = @OrderID");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in OrderHeadDB, DeleteOrderHead");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("OrderID", SqlDbType.Int).Value = orderID;
                //        return command.ExecuteNonQuery();
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in OrderHeadDB, DeleteOrderHead", ex);
            }
        }
    }
}

